<?php

/**
	CyberFish REST API  admin
*/

namespace cf\api\admin;
require_once dirname(__FILE__).'/../config.php';
require_once \cf\Config::path.'db.php';
require_once \cf\Config::path.'user.php';

function getCategories($parentId=null, &$items=array())
{
	$q = null;
	\cf\createStaticQuery($q,"
		SELECT id, name, url, tree_id, list_id
		FROM cf_admin_categories
		WHERE parent_id=:parent OR (parent_id IS NULL AND :parent IS NULL)
		ORDER BY sort_order
	");
	$q->setParam('parent',$parentId);
	$q->execute();
	while ($q->fetch()) {
		$item = array(
			'id' => $q->at('id'),
			'name' => $q->at('name'),
			'url' => ($q->at('url') ? $q->at('url').'&mid='.$q->at('id') : (canRead($q->at('list_id')) ? 'list.php?vid='.$q->at('list_id').'&mid='.$q->at('id') : '')),
			'children' => array()
		);
		getCategories($q->at('id'),$item['children']);
		if ($q->at('tree_id') && canRead($q->at('tree_id'))) {
			getTree($q->at('tree_id'), null, $item['children'], ($q->at('list_id') && canRead($q->at('list_id')))?$q->at('list_id'):false, $q->at('id'));
		}
		if (count($item['children']) || $item['url']) {
			if (!$item['url']) {
				$item['url'] = 'category.php?id='.$q->at('id').'&mid='.$q->at('id');
			}
			$items[] = $item;
		}
	}
	$q->close();

	return $items;
}

function canRead($vid)
{
	$user = \cf\User::getLoggedIn();
	if (!$user) {
		return false;
	}
	if (!$vid) {
		return false;
	}
	$canRead = false;
	$grants = \cf\query2vector('
		SELECT can_read 
		FROM cf_role_view_grants 
		WHERE (view_id=:vid OR view_id IS NULL)
		  AND (role_id IN (SELECT role_id FROM cf_user_roles WHERE user_id=:uid) OR role_id IS NULL)
		ORDER BY role_id, view_id',
		array('vid'=>$vid, 'uid'=>$user->id())
	);
	
	foreach ($grants as $g) {
		$canRead = (bool)$g;
	}
	return $canRead;
}


function canExecute($aid)
{
	$user = \cf\User::getLoggedIn();
	if (!$user) {
		return false;
	}
	$canExecute = false;
	$grants = \cf\query2vector('
		SELECT can_execute 
		FROM cf_role_action_grants 
		WHERE (action_id=:aid OR action_id IS NULL)
		  AND (role_id IN (SELECT role_id FROM cf_user_roles WHERE user_id=:uid) OR role_id IS NULL)
		ORDER BY role_id, action_id',
		array('aid'=>$aid, 'uid'=>$user->id())
	);
	
	foreach ($grants as $g) {
		$canExecute = (bool)$g;
	}
	return $canExecute;
}

function getView($id)
{
	$user = \cf\User::getLoggedIn();
	if (!$user) {
		return false;
	}
	//current_user_can('read',$id)
	
	$view = \cf\query2array("
		SELECT 
			cf_admin_view.*, 
			cf_admin_view_settings.id AS settings_id,
			cf_admin_view_settings.sort_field AS sort_field, 
			cf_admin_view_settings.sort_dir AS sort_dir,
			cf_admin_view_settings.records_on_page AS records_on_page
		FROM cf_admin_view
		LEFT JOIN cf_admin_view_settings ON view_id=cf_admin_view.id AND user_id={$user->id()}
		WHERE cf_admin_view.id=:id",
		array('id'=>$id)
	);
	$view['fields'] = \cf\query2arrays('
		SELECT name,title,type_id,hint,IFNULL(cf_admin_view_settings_field.sort_order,cf_admin_view_field.sort_order) AS so
		FROM cf_admin_view_field 
		LEFT JOIN cf_admin_view_settings_field ON cf_admin_view_field.name=cf_admin_view_settings_field.field_name AND cf_admin_view_settings_field.settings_id=:settings_id
		WHERE view_id=:id 
		  AND IFNULL(cf_admin_view_settings_field.sort_order,cf_admin_view_field.sort_order)>0
		ORDER BY so',
		array('id'=>$id,'settings_id'=>$view['settings_id'])
	);

	$view['allfields'] = \cf\query2arrays('
		SELECT name,title,type_id,hint FROM cf_admin_view_field 
		WHERE view_id=:id
		ORDER BY title',
		array('id'=>$id)
	);
	
	$view['edit_action'] = \cf\query2array('
		SELECT cf_admin_action.id, cf_admin_action.url
		FROM cf_admin_view_action INNER JOIN cf_admin_action ON action_id=cf_admin_action.id
		WHERE view_id=:id AND sort_order=1',
		array('id'=>$id)
	);
	
	$view['add_action'] = \cf\query2array('
		SELECT cf_admin_action.id, cf_admin_action.url
		FROM cf_admin_view_action INNER JOIN cf_admin_action ON action_id=cf_admin_action.id
		WHERE view_id=:id AND sort_order=2',
		array('id'=>$id)
	);
	
	$view['del_action'] = \cf\query2var('
		SELECT action_id FROM cf_admin_view_action WHERE view_id=:id AND sort_order=3',
		array('id'=>$id)
	);
	
	$view['actions'] = \cf\query2arrays('
		SELECT action_id AS id, cf_admin_action.name AS name, sort_order
		FROM cf_admin_view_action
		INNER JOIN cf_admin_action ON cf_admin_view_action.action_id = cf_admin_action.id
		WHERE view_id=:id AND sort_order NOT IN (1,2,3)',
		array('id'=>$id)
	);
	return $view;
}

function getListHeader($vid /* ,$sid=0 - settings id*/ )
{
	$view = getView($vid);
	return $view['fields'];
}

function getListLength($id, $search=null, $filter=array())
{
	$view = getView($id);
	$params = array();
	$sqlWhere = ListImpl::paramsAsSQL($search, $filter, $view['fields'], $params);
	return \cf\query2var(
		'SELECT COUNT(*) FROM '.(preg_match('/^\w+$/',$view['query']) ? $view['query'] : '('.$view['query'].') t').
		($sqlWhere?" WHERE $sqlWhere":''),
		$params
	);
}

/**
	@param $sort     array(fname=>a/d)
	@param $search   string to look for in EVERY field
	@param $filter   array(fname => filter_val | array(filter_val|array(filter_val),filter_type) ) filter_type: =,<,>
	@return  first field in record is admin_view.primary_key_field or empty string
*/
function getList($id, $skip=0, $limit=0, $sort=array(), $search=null, $filter=array(), $brief=false, $addHeader=false, $params=array())
{
	$user = \cf\User::getLoggedIn();
	
	$view = getView($id);
	$sql = preg_match('/^\w+$/',$view['query']) ? $view['query'] : '('.$view['query'].')';
	eval('$sql="'.$sql.'";');
	$fields = getListHeader($id);
	$sqlSelect = $view['primary_key_field'] ? $view['primary_key_field'] : "''";
	if ($brief) {
		$sqlSelect .= ',' . $view['name_field'];
	} else {
		foreach ($fields as $f) {
			$sqlSelect .= ',' . $f['name'];
		}
	}
	
	$sqlOrder = '';
	foreach ($sort as $fnm => $d) {
		$sqlOrder .= ($sqlOrder?',':'')."$fnm ".($d=='a' ? 'ASC' : 'DESC');
	}
	
	$sqlWhere = ListImpl::paramsAsSQL($search, $filter, $view['fields'], $params);
	
	$recs = \cf\query2arrays(
				\cf\limitSelect(
					"SELECT $sqlSelect FROM $sql t".($sqlWhere?" WHERE $sqlWhere":'').($sqlOrder?" ORDER BY $sqlOrder":''), 
					$limit, 
					$skip),
				$params,
				true
	);
	
	return $addHeader ? array($brief ? array( array('name'=>$view['name_field'],'title'=>$view['name'],'hint'=>$view['name']) ): $fields, $recs) : $recs;
}


function findByKey($vid, $k, $brief=false, $params=array())
{
	$view = getView($vid);
	$sql = preg_match('/^\w+$/',$view['query']) ? $view['query'] : '('.$view['query'].')';
	$fields = getListHeader($vid);
	$sqlSelect = '';
	if ($brief) {
		$sqlSelect .= $view['name_field'];
	} else {
		foreach ($fields as $f) {
			$sqlSelect .= ($sqlSelect ? ',':'') . $f['name'];
		}
	}
	$params['k']=$k;
	return \cf\query2array(
		"SELECT $sqlSelect FROM $sql t WHERE ".$view['primary_key_field']."=:k",
		$params
	);
}


/*  obsolete, replace with findByKey() */
function getNameByKey($vid, $k)
{
	$view = getView($vid);
	$sql = preg_match('/^\w+$/',$view['query']) ? $view['query'] : '('.$view['query'].')';
	return \cf\query2var(
		'SELECT '.$view['name_field']." FROM $sql t WHERE ".$view['primary_key_field']."=:k",
		array('k'=>$k)
	);
}

function getTree($id, $parentId=null, &$items=array(), $listId=false, $menuId=false)
{
	$view = getView($id);
	
	if (!$listId) {
		$listId = $id;
	}
	
	$q = null;
	\cf\createStaticQuery($q,
		'SELECT '.$view['primary_key_field'].', '.$view['name_field'].
		' FROM '.(preg_match('/^\w+$/',$view['query']) ? $view['query'] : '('.$view['query'].')').' t'.
		' WHERE '.$view['foreign_key_field'].'=:parent OR ('.$view['foreign_key_field'].' IS NULL AND :parent IS NULL)'
	);
	$q->setParam('parent',$parentId);
	$q->execute();
	while ($q->fetch(DB_FETCH_NUM)) {
		$pid = $q->at(0);
		$mid = $menuId ? $menuId.$view['id'].$pid : $pid;
		$item = array(
			'id' => $mid,
			'name' => $q->at(1),
			'url' => $view['url']."?vid=$listId&fk=$pid&tid=$id&mid=$mid",
			'children' => array()
		);
		getTree($id, $pid, $item['children'], $listId, $menuId ? $mid : false);
		$items[] = $item;
	}
	$q->close();
	
	return $items;
}

function getAction($id, $params=array())
{
	$action = \cf\query2array('
		SELECT * FROM cf_admin_action WHERE id=:id',
		array('id'=>$id)
	);

	$action['params'] = array();
	$action['params_query'] = '';
	if ($action['params_id']) {
		$action['params'] = \cf\query2arrays('
			SELECT name, type_id AS type, title, default_val AS value, mandatory, max_length, min_length, fmt, default_val, tree_id, list_id 
			FROM cf_admin_action_param 
			WHERE params_id=:id
			ORDER BY sort_order',
			array('id'=>$action['params_id'])
		);
		$action['params_query'] = \cf\query2var('SELECT query FROM cf_admin_action_params WHERE id=:id', array('id'=>$action['params_id']));
		if (!empty($params) && $action['params_query']) {
			$values = \cf\query2array($action['params_query'], $params);
			for ($i=0; $i<count($action['params']); ++$i) {
				if (array_key_exists($action['params'][$i]['name'],$values)) {
					$action['params'][$i]['value'] = $values[$action['params'][$i]['name']];
					if ($action['params'][$i]['type'] == 'File') {
						$action['params'][$i]['is_image'] = @getimagesize($_SERVER['DOCUMENT_ROOT'].'/'.$action['params'][$i]['value']) != false;
					}
				}
			}
		}
		
		for ($i=0; $i<count($action['params']); ++$i) {
			$p = $action['params'][$i];
			$action['params'][$i]['dict_value'] = false;
			if ($p['tree_id'] || $p['list_id']) {
				$v = getView($p['tree_id'] ? $p['tree_id'] : $p['list_id']);
				$action['params'][$i]['dict_value'] = \cf\query2var(
					'SELECT '.$v['name_field'].
					' FROM '.(preg_match('/^\w+$/',$v['query']) ? $v['query'] : '('.$v['query'].') t').
					' WHERE '.$v['primary_key_field'].'=:id',
					array('id'=>$p['value'])
				);
			}
		}
	}
	return $action;
}

function doAction($id, $params=array())
{
	$user = \cf\User::getLoggedIn();
	
	$sql = \cf\query2var('SELECT query FROM cf_admin_action WHERE id=:id', array('id'=>$id));
	eval('$sql="'.$sql.'";');
	
	foreach ($params as $nm => $v) {
		$params[$nm] = $v ? $v : null;
	}
	
	$fileParams = \cf\query2arrays("
		SELECT cf_admin_action_param.name AS name, fmt
		FROM cf_admin_action_param
		INNER JOIN cf_admin_action_params ON cf_admin_action_param.params_id = cf_admin_action_params.id
		INNER JOIN cf_admin_action ON cf_admin_action_params.id = cf_admin_action.params_id
		WHERE cf_admin_action.id=:id AND cf_admin_action_param.type_id='File'",
		array('id'=>$id)
	);
	foreach ($fileParams as $param) {
		$nm = $param['name'];
		if ($params[$nm][0]=="\t") {  //tab at the beginning: delete file
			@unlink(\cf\Config::root_path.substr($params[$nm],1));
			$params[$nm] = null;
		}
		if (array_key_exists($nm,$_FILES)) {
			list($uploadDir, $ext) = explode(';',$param['fmt']);
			$fparams = $_FILES[$nm];
			if ($fparams['type']=='url') {		//URL passed
				$url = $fparams['name'];
				$urlInfo = parse_url($url);
				if (in_array($urlInfo['scheme'],stream_get_wrappers())) {   //are such URLs supported
					$fname = pathinfo($urlInfo['path']);
					if ( $ext != '*' && !in_array(strtolower($fname['extension']),explode(',',strtolower($ext))) ) {
						continue;	//invalid file type
					}
					$path = "$uploadDir/".uniqid($nm).'.'.$fname['extension'];
					if (copy($url,\cf\Config::root_path."$path")) {
						@unlink(\cf\Config::root_path.$params[$nm]);
						chmod(\cf\Config::root_path."$path", 0644);
						$params[$nm] = $path;					
					}
				}
			} else {	//file uploaded
				if (UPLOAD_ERR_OK != $fparams['error'] || !is_uploaded_file($fparams['tmp_name'])) {
					continue;	 //if a file is not uploaded check upload_max_filesize or post_max_size in php.ini
				}
			
				$fname = pathinfo($fparams['name']);			
				if ( $ext != '*' && !in_array(strtolower($fname['extension']),explode(',',strtolower($ext))) ) {
					continue;	//invalid file type
				}
			
				$path = "$uploadDir/".uniqid($nm).'.'.$fname['extension'];
				if (move_uploaded_file($fparams['tmp_name'],\cf\Config::root_path."$path")) {
					@unlink(\cf\Config::root_path.$params[$nm]);
					chmod(\cf\Config::root_path."$path", 0644);
					$params[$nm] = $path;
				}
			}
		} 
	}

	\cf\execQuery($sql, $params);
	
	global $db;
	return $db->lastInsertId();
}

/**
	@param $bulkParams array( array('paramName1'=>paramVal1,...) )
*/
function doActionBulk($id, $bulkParams, $params=array())
{
	$user = \cf\User::getLoggedIn();

	$sql = \cf\query2var('SELECT query FROM cf_admin_action WHERE id=:id', array('id'=>$id));
	eval('$sql="'.$sql.'";');
	
	foreach ($params as $nm => $v) {
		$params[$nm] = $v ? $v : null;
	}
	$q = \cf\createQuery($sql, $params);
	foreach ($bulkParams as $p) {
		$q->setParams($p)->execute();
	}
}

function doRelatedActions($id1, $params1, $id2, $bulkParams2, $idName=false, $params2=array())
{
	$newId = doAction($id1, $params1);
	if ($idName) {
		$params2[$idName] = $newId;
	}
	doActionBulk($id2, $bulkParams2, $params2);
	return $newId;
}

/**
	also deletes files
*/
function doDelete($aid, $params)
{
	$user = \cf\User::getLoggedIn();
		
	if (!array_key_exists(0,$params)) {
		$params = array($params);
	}
	
	$action = getAction($aid);
	$qFiles = null;
	if ($action['params_query']) {
		$fileFields = array();
		foreach ($action['params'] as $param) {
			if ($param['type'] == 'File') {
				$fileFields[] = $param['name'];
			}
		}
		if (!empty($fileFields)) {
			$qFiles = \cf\createQuery('SELECT '.implode(',',$fileFields).' FROM ('.$action['params_query'].') t');
		}
	}
	
	eval('$sql="'.$action['query'].'";');
	$q = \cf\createQuery($sql);
	foreach ($params as $p) {
		if ($qFiles) {
			foreach ($qFiles->setParams($p)->execute()->fetch() as $f) {
				@unlink($_SERVER['DOCUMENT_ROOT']."/$f");
			}
		}
		$q->setParams($p)->execute();
	}
}



class ListImpl
{
	static public function paramsAsSQL($search, $filter, $fields, &$params)
	{
		$where = '';
		if ($search) {		//every word must be found in at leats one field
			$words = preg_split('/\s+/',$search);
			for ($i=0; $i<count($words); ++$i) {
				$s = '';
				foreach ($fields as $f) {
					$s .= ($s?' OR ':'').$f['name']." LIKE CONCAT('%',:search$i,'%')";
				}
				$where .= ($where?' AND ':'')."($s)";
			}
			for ($i=0; $i<count($words); ++$i) {
				$params["search$i"] = $words[$i];
			}
		}
		
		foreach ($filter as $fnm => $flt) {
			if (is_array($flt)) {
				list($val, $tp) = $flt;
			} else {
				$val = $flt;
				$tp = '=';
			}

			$fieldWhere = '';
			if (is_array($val)) {
				for ($i=0; $i<count($val); ++$i) {
					$fieldWhere .= ($i>0 ? ' OR ' : '') . "$fnm $tp :filter$fnm$i";
					$params["filter$fnm$i"] = $val[$i];
				}
				$fieldWhere = "($fieldWhere)";
			} else {
				if (is_null($val)) {
					$fieldWhere = "$fnm IS NULL";
				} else {
					if ($tp == 'like') {
						$val = "%$val%";
					} else if ($tp=='on') {
						$tp = '>=';
						$val = 1;
					}
					if ($tp=='off') {
						$fieldWhere = "($fnm IS NULL OR $fnm=0)";
					} else {
						$fieldWhere = "$fnm $tp :filter$fnm";
						$params["filter$fnm"] = $val;
					}
				}
			}
			$where .= ($where?' AND ':'').$fieldWhere;
		}
		
		return $where;
	}
}

?>